﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.IO;
using PerCiGal.Types;

namespace PerCiGal.DAL
{
    /// <summary>
    /// 
    /// </summary>
    public class DALCollection
    {
        public DALCollection()
        {

        }

        /// <summary>
        /// Create a new collection
        /// </summary>
        /// <param name="collectionInfo">collection info</param>
        public void Create(Types.COLLECTION_INFO collectionInfo)
        {
            try
            {
                string connStr = DALHelper.GetConnectionString(collectionInfo.FILE_PATH);

                /* Create database file */
                var engine = new SqlCeEngine(connStr);
                engine.CreateDatabase();

                /* create table and columns */
                using (var conn = new SqlCeConnection(connStr))
                {
                    var commandScript = new List<string>();

                    try
                    {
                        conn.Open();

                        #region Collection

                        commandScript.AddRange(DALHelper.GETSQLCommandList("Create.Collection.sqlce"));

                        #endregion

                        #region Movie

                        commandScript.AddRange(DALHelper.GETSQLCommandList("Create.Movie.sqlce"));

                        #endregion

                        #region Default Values

                        commandScript.AddRange(DALHelper.GETSQLCommandList("Create.Default_Values_EN.sqlce"));

                        #endregion

                        foreach (string sqlCommand in commandScript)
                        {
                            if (!string.IsNullOrEmpty(sqlCommand.Trim()))
                            {
                                using (var cmd = new SqlCeCommand(sqlCommand, conn))
                                {
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Insert(Types.COLLECTION_INFO collectionInfo)
        {
            try
            {
                string connStr = DALHelper.GetConnectionString(collectionInfo.FILE_PATH);

                using (var conn = new SqlCeConnection(connStr))
                {
                    try
                    {
                        conn.Open();

                        string commandText = DALHelper.GetsqlCommandText("Insert.COLLECTION_INFO.sqlce");

                        using (var cmd = new SqlCeCommand(commandText, conn))
                        {
                            cmd.Parameters.Add(SqlceHelper.ParamString("@name", collectionInfo.NAME));
                            cmd.Parameters.Add(SqlceHelper.ParamString("@pwd", collectionInfo.PWD));
                            cmd.Parameters.Add(SqlceHelper.ParamString("@version", collectionInfo.VERSION));
                            cmd.Parameters.Add(SqlceHelper.ParamObj("@picture", collectionInfo.PICTURE));
                            cmd.Parameters.Add(SqlceHelper.ParamString("@file_path", collectionInfo.FILE_PATH));
                            cmd.Parameters.Add(SqlceHelper.ParamDateTime("@dt_creation", collectionInfo.DT_CREATION));
                            cmd.Parameters.Add(SqlceHelper.ParamDateTime("@dt_last_modified",
                                                                         collectionInfo.DT_LAST_MODIFIED));

                            cmd.ExecuteNonQuery();
                        }

                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Types.COLLECTION_INFO CollectionInfo(string filePath, string password)
        {
            try
            {
                string connStr = DALHelper.GetConnectionString(filePath);

                var retVal = new COLLECTION_INFO();

                using (var conn = new SqlCeConnection(connStr))
                {
                    try
                    {
                        conn.Open();

                        string commandText = DALHelper.GetsqlCommandText("Select.COLLECTION_INFO.sqlce");

                        using (var cmd = new SqlCeCommand(commandText, conn))
                        {
                            var myDataReader = cmd.ExecuteReader();

                            while (myDataReader.Read())
                            {
                                retVal.NAME = SqlceHelper.DataReaderString(myDataReader, "NAME");
                                retVal.VERSION = SqlceHelper.DataReaderString(myDataReader, "VERSION");
                                retVal.PWD = SqlceHelper.DataReaderString(myDataReader, "PWD");
                                retVal.FILE_PATH = SqlceHelper.DataReaderString(myDataReader, "FILE_PATH");
                                retVal.PICTURE =
                                    (byte[])SqlceHelper.DataReaderObject(myDataReader, "PICTURE");
                                retVal.DT_CREATION = SqlceHelper.DataReaderDateTime(myDataReader,
                                                                                                   "DT_CREATION");
                                retVal.DT_LAST_MODIFIED = SqlceHelper.DataReaderDateTime(myDataReader,
                                                                                                        "DT_LAST_MODIFIED");
                            }
                        }

                        return retVal;

                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

                return retVal;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<Types.GENRE> Genres(Types.COLLECTION_INFO collectionInfo)
        {
            try
            {
                var retVal = new List<GENRE>();

                string connStr = DALHelper.GetConnectionString(collectionInfo.FILE_PATH);

                using (var conn = new SqlCeConnection(connStr))
                {
                    try
                    {
                        conn.Open();

                        string commandText = DALHelper.GetsqlCommandText("Select.GENRES.sqlce");

                        using (var cmd = new SqlCeCommand(commandText, conn))
                        {
                            var myDataReader = cmd.ExecuteReader();

                            while (myDataReader.Read())
                            {
                                var genre = new GENRE();

                                genre.COD_GENRES = SqlceHelper.DataReaderInt(myDataReader, "COD_GENRES");
                                genre.GENRE_DESCRIPTION = SqlceHelper.DataReaderString(myDataReader, "GENRE_DESCRIPTION");

                                retVal.Add(genre);
                            }
                        }

                        return retVal;
                    }
                    catch (Exception exc)
                    {
                        throw exc;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch(Exception exc)
            {
                throw exc;
            }

        }

        public List<Types.NATION> Nations(Types.COLLECTION_INFO collectionInfo)
        {
            try
            {
                var retVal = new List<NATION>();

                string connStr = DALHelper.GetConnectionString(collectionInfo.FILE_PATH);

                using (var conn = new SqlCeConnection(connStr))
                {
                    try
                    {
                        conn.Open();

                        string commandText = DALHelper.GetsqlCommandText("Select.NATIONS.sqlce");

                        using (var cmd = new SqlCeCommand(commandText, conn))
                        {
                            var myDataReader = cmd.ExecuteReader();

                            while (myDataReader.Read())
                            {
                                var nation = new NATION();

                                nation.COD_NATIONS = SqlceHelper.DataReaderInt(myDataReader, "COD_NATIONS");
                                nation.NATION_DESCRIPTION = SqlceHelper.DataReaderString(myDataReader,
                                                                                         "NATION_DESCRIPTION");

                                retVal.Add(nation);
                            }
                        }

                        return retVal;
                    }
                    catch (Exception exc)
                    {
                        throw exc;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception exc)
            {
                throw exc;
            }

        }

        public List<Types.SUBTITLE_LANGUAGE> SubtitleLanguages(Types.COLLECTION_INFO collectionInfo)
        {
            try
            {
                var retVal = new List<SUBTITLE_LANGUAGE>();

                string connStr = DALHelper.GetConnectionString(collectionInfo.FILE_PATH);

                using (var conn = new SqlCeConnection(connStr))
                {
                    try
                    {
                        conn.Open();

                        string commandText = DALHelper.GetsqlCommandText("Select.SUBTITLE_LANGUAGES.sqlce");

                        using (var cmd = new SqlCeCommand(commandText, conn))
                        {
                            var myDataReader = cmd.ExecuteReader();

                            while (myDataReader.Read())
                            {
                                var subtitleLanguage = new SUBTITLE_LANGUAGE();

                                subtitleLanguage.COD_SUBTITLE_LANGUAGES = SqlceHelper.DataReaderInt(myDataReader,
                                                                                                    "COD_SUBTITLE_LANGUAGES");
                                subtitleLanguage.SUBTITLE_LANGUAGES_DESCRIPTION =
                                    SqlceHelper.DataReaderString(myDataReader, "SUBTITLE_LANGUAGES_DESCRIPTION");

                                retVal.Add(subtitleLanguage);
                            }
                        }

                        return retVal;
                    }
                    catch (Exception exc)
                    {
                        throw exc;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception exc)
            {
                throw exc;
            }

        }
    }
}
